CREATE PROCEDURE [dbo].[asi_UpdateContactSalutation]
    (@inputSalutationKey uniqueidentifier)
AS
BEGIN

SET NOCOUNT ON

DECLARE @sql nvarchar(2000)
DECLARE @contactKey uniqueidentifier
DECLARE @IndividualFormula nvarchar(2000)
DECLARE @InstituteFormula nvarchar(2000)

DECLARE @calcFormula nvarchar(2000)

DECLARE @tempKey uniqueidentifier
DECLARE @tempValue nvarchar(1000)

DECLARE @isDeletable as bit
DECLARE @autoCreate as bit

SELECT @IndividualFormula = IndividualFormula, @InstituteFormula = InstituteFormula, @autoCreate = AutoCreate
    FROM vBoSalutationRef
    WHERE SalutationKey = @inputSalutationKey
    
IF @@ROWCOUNT = 0
    RETURN

IF @autoCreate = 1
    select @isDeletable = 0
ELSE
    select @isDeletable = 1

DECLARE theCursor CURSOR FAST_FORWARD FOR
    SELECT ContactKey
        FROM vBoContactSalutation
        WHERE SalutationKey = @inputSalutationKey
            AND IsOverridden = 0

OPEN theCursor
FETCH NEXT FROM theCursor INTO @contactKey

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @tempKey = newid()
    
    SELECT @sql =  
        CASE
        WHEN IsInstitute = 1 THEN
            ' asi_ProcessFormulaOutput '
                + '''' + convert (nvarchar(100), @tempKey) + ''', '
                + '''' + @InstituteFormula + ''', '
                + '''vBoInstitute'', ''ContactKey'', '
                + '''' + convert (nvarchar(50), @contactKey)
                + ''''
    
        WHEN IsInstitute = 0 THEN
            ' asi_ProcessFormulaOutput '
                + '''' + convert (nvarchar(100), @tempKey) + ''', '
                + '''' + @IndividualFormula + ''', '
                + '''vBoIndividual'', ''ContactKey'', '
                + '''' + convert (nvarchar(50), @contactKey)
                + ''''
        END
    FROM vBoContact WHERE ContactKey = @contactKey
    
    EXEC (@sql)
    
    SELECT @tempValue = tempValue from tempFormula
        WHERE tempKey = @tempKey
    
    IF datalength (@tempValue) > 0 AND @tempValue IS NOT NULL
        UPDATE vBoContactSalutation SET SalutationText = @tempValue, IsDeletable = @isDeletable
        WHERE ContactKey = @contactKey and SalutationKey = @inputSalutationKey
    
    DELETE FROM tempFormula WHERE tempKey = @tempKey
    
    FETCH NEXT FROM theCursor INTO @contactKey
END

CLOSE theCursor
DEALLOCATE theCursor

UPDATE SalutationRef SET NeedsUpdateFlag = 0 WHERE SalutationKey = @inputSalutationKey

SET NOCOUNT OFF

END

GO